# @Time : 2022/9/27 13:07
# @Author : zhangzhihui
# @Email : mrzhangzh@gmail.com
import pymysql
import hashlib, json, base64
import requests

def notice_messge():
    sql = """
            SELECT
            u.mobile,
            a.document_number as number,
            'gw' as type 
            FROM administrative_conferencememo a
            INNER JOIN administrative_conferencememopeople b on a.id = b.notice_id_id
            INNER JOIN tb_users_message u on u.id = b.audit_people_id
            INNER JOIN administrative_company c ON u.company_id = c.id
            WHERE a.type not in ('e') and b.state = 'accept' and u.is_delete = false and c.is_functional = FALSE 

            UNION 

            SELECT
            u.mobile,
            a.recive_number as number,
            'lw' as type 
            FROM administrative_officialdocuments a
            INNER JOIN administrative_officialdocumentspeople b on b.notice_id_id = a.id
            INNER JOIN tb_users_message u on u.id = b.audit_people_id 
            INNER JOIN administrative_company c ON u.company_id = c.id
            WHERE b.state = 'accept' and u.is_delete = false and c.is_functional = FALSE  

            """
    db = pymysql.connect(host="127.0.0.1", user='root', password='jsyy2021', port=3306, db="administration",
                          use_unicode=True, charset='utf8')
    cursor = db.cursor()
    cursor.execute(sql)
    rows = cursor.fetchall()
    record_list = []
    record_dict = {}
    # print(rows)
    for row in rows:
        if row[0] in record_dict:
            record_dict[row[0]].append({row[2]: row[1]})
        else:
            record_dict[row[0]] = [{row[2]: row[1]}]
    # print(record_dict)
    for k, v in record_dict.items():
        # for v1 in v:
        record_list.append({'mobile': k, 'gw_number_list': [v1['gw'] for v1 in v if 'gw' in v1],
                            'lw_number_list': [v1['lw'] for v1 in v if 'lw' in v1]})
    # print(record_list)
    for record in record_list:
        gw_str = ''
        lw_str = ''
        if len(record['gw_number_list']) > 0:
            gw_number_str = ', '.join(record['gw_number_list'])
            gw_str = "待收阅的公文需要处理[%(gw_number_str)s], (共%(num)s条)，" % {'gw_number_str': gw_number_str,
                                                                      'num': len(record['gw_number_list'])}
        if len(record['lw_number_list']) > 0:
            lw_number_str = ', '.join(record['lw_number_list'])
            lw_str = "待收阅的上级来文需要处理[%(lw_number_str)s], (共%(num)s条)，" % {'lw_number_str': lw_number_str,
                                                                        'num': len(record['lw_number_list'])}
        mssage_str = '您有%(gw_str)s%(lw_str)s请登录OA系统（网址：http://192.168.60.200:8000/）在“流程管理—我的待办”中收阅文件。另外在“我的收文”中可以查阅我已经收阅的所有文件。' % {
            'gw_str': gw_str, 'lw_str': lw_str}
        print(mssage_str)
        send_mssage([record['mobile']], mssage_str)


def md5value(key):
    input_name = hashlib.md5()
    input_name.update(key.encode("utf-8"))
    return (input_name.hexdigest()).lower()


def send_mssage(mobiles, content):
    ecName = "京山市人民医院"
    apId = "oapro"
    mobiles = ",".join(mobiles)
    content = content
    sign = "D32WddTwR"
    addSerial = ""

    # 生成md5的mac值，将ecName、apId、secretKey、mobiles、content、sign、addSerial进行无间隔拼接后生成32位的小写md5值
    g_mac = hashlib.md5()
    g_mac_cont = ecName + apId + 'Jsyy@2020' + mobiles + content + \
                 sign + addSerial

    g_mac.update(g_mac_cont.encode('utf-8'))
    content_a = {
        "ecName": ecName,
        "apId": apId,
        "mobiles": mobiles,
        "content": content,
        "sign": sign,
        "addSerial": addSerial,
        "mac": md5value(g_mac_cont),
    }
    # content_a["mac"] = md5value(g_mac_cont)

    # 将content_a转为json字串后进行base64编码加密
    encoding_content = base64.b64encode(json.dumps(content_a).encode('utf-8'))
    strs = str(encoding_content, 'utf-8')
    print("base64字符串：", g_mac_cont)
    # 发送信息，更多接口见后面

    response = requests.post(url="http://112.35.1.155:1992/sms/norsubmit", data=strs,
                             headers={'Content-Type': 'application/json', 'charset': 'UTF-8'})

    # 读取响应
    print(response.json())
    return response.json()


if __name__ == '__main__':
    notice_messge()